if exists (select name from sysobjects where name ='spu_danhMuc_layDanhSachTheoLoaiDanhMuc')
	drop proc spu_danhMuc_layDanhSachTheoLoaiDanhMuc
go
create proc spu_danhMuc_layDanhSachTheoLoaiDanhMuc(@LoaiDanhMuc TINYINT)
as
	select * from DanhMuc where LoaiDanhMuc=@LoaiDanhMuc
go

if exists (select name from sysobjects where name ='spu_noiDen_layDanhSachTheoIsTrongNuoc')
	drop proc spu_noiDen_layDanhSachTheoIsTrongNuoc
go
CREATE PROC spu_noiDen_layDanhSachTheoIsTrongNuoc(@IsTrongNuoc TINYINT)
AS
	SELECT * FROM dbo.NoiDen WHERE IsTrongNuoc=@IsTrongNuoc
go	

/* tour nuoc ngoai--------------------------- */
if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_capNhatSoLuongConNhanTheoId')
	drop proc spu_tourDuLichNuocNgoai_capNhatSoLuongConNhanTheoId
go
CREATE PROC spu_tourDuLichNuocNgoai_capNhatSoLuongConNhanTheoId(@SoChoConNhan INT,@Id DATETIME)
AS
	UPDATE dbo.TourDuLichNuocNgoai SET SoChoConNhan=@SoChoConNhan WHERE Id=@Id
GO

if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_laySoLuong')
	drop proc spu_tourDuLichNuocNgoai_laySoLuong
go
CREATE PROC spu_tourDuLichNuocNgoai_laySoLuong(@NoiDi NVARCHAR(50), @NoiDen NVARCHAR(50), @NgayDi DATETIME, @GiaTu DECIMAL, @GiaDen DECIMAL)
AS
	select COUNT(*)
	from dbo.TourDuLichNuocNgoai
	where (@NoiDi ='' OR NoiDi=@NoiDi) AND (@NoiDen ='' OR NoiDen=@NoiDen) AND (@NgayDi IS NULL OR (DAY(NgayGioDi)=DAY(@NgayDi) AND MONTH(NgayGioDi)=MONTH(@NgayDi) AND YEAR(NgayGioDi)=YEAR(@NgayDi))) AND (@GiaTu IS NULL OR GiaTourCoBanNguoiLon>=@GiaTu) AND (@GiaDen IS NULL OR GiaTourCoBanNguoiLon<=@GiaDen) AND TinhTrangTour=0
GO

if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_traCuu')
	drop proc spu_tourDuLichNuocNgoai_traCuu
go
CREATE PROC spu_tourDuLichNuocNgoai_traCuu(@NoiDi NVARCHAR(50), @NoiDen NVARCHAR(50), @NgayDi DATETIME, @GiaTu DECIMAL, @GiaDen DECIMAL, @start INT, @end INT)
AS
	with Rows as
	(
		select Id,MaTour,TenTour,NoiDen,NoiDi AS NoiKhoiHanh,NgayGioDi,NgayGioVe,GiaTourCoBanNguoiLon AS Gia,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay,ROW_NUMBER() Over (order by NgayGioDi) as Row
		from dbo.TourDuLichNuocNgoai
		where (@NoiDi ='' OR NoiDi=@NoiDi) AND (@NoiDen ='' OR NoiDen=@NoiDen) AND (@NgayDi IS NULL OR (DAY(NgayGioDi)=DAY(@NgayDi) AND MONTH(NgayGioDi)=MONTH(@NgayDi) AND YEAR(NgayGioDi)=YEAR(@NgayDi))) AND (@GiaTu IS NULL OR GiaTourCoBanNguoiLon>=@GiaTu) AND (@GiaDen IS NULL OR GiaTourCoBanNguoiLon<=@GiaDen) AND TinhTrangTour=0
	)
	select top (@end) Id,MaTour,TenTour,NoiDen,NoiKhoiHanh,NgayGioDi,NgayGioVe,Gia,SoChoConNhan,SoNgay
	from Rows
	where Row>@start
	--SELECT Id,MaTour,TenTour,NgayGioDi,NgayGioVe,GiaTourCoBanNguoiLon AS Gia,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay
	--FROM dbo.TourDuLichNuocNgoai
	--WHERE (@NoiDi ='' OR NoiDi=@NoiDi) AND (@NoiDen ='' OR NoiDen=@NoiDen) AND (@NgayDi IS NULL OR (DAY(@NgayDi)=DAY(NgayGioDi) AND MONTH(@NgayDi)=MONTH(NgayGioDi) AND YEAR(@NgayDi)=YEAR(NgayGioDi))) AND (@GiaTu IS NULL OR @GiaTu<=GiaTourCoBanNguoiLon) AND (@GiaDen IS NULL OR @GiaDen>=GiaTourCoBanNguoiLon) AND TinhTrangTour=0
GO

if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_layChiTietTour')
	drop proc spu_tourDuLichNuocNgoai_layChiTietTour
go
CREATE PROC spu_tourDuLichNuocNgoai_layChiTietTour(@IdTour DATETIME)
AS
	SELECT t.Id,MaTour,TenTour,NoiDen,NoiDi,NgayGioDi,NgayGioVe,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay,ChuyenBayDi,ChuyenBayVe,h.HoTen,h.DiaChi,h.DienThoai,NoiTapTrung,NgayGioTapTrung,GiaTourCoBanNguoiLon,GiaTourCoBanTreEm,GiaTourCoBanTreNho,PhuThuXangDauNguoiLon,PhuThuXangDauTreEm
	,PhuThuXangDauTreNho,LePhiSanBayNguoiLon,LePhiSanBayTreEm,LePhiSanBayTreNho,NgayGioHopDoan,PhuThuVisaTaiNhapVietNam,PhuThuPhongDon,PhuThuVisaNguoiLon,PhuThuVisaTreEm,PhuThuVisaTreNho
	FROM dbo.TourDuLichNuocNgoai t LEFT JOIN dbo.HuongDanVienDuLich h ON t.IdHuongDanVienDuLich=h.Id 
	WHERE t.Id=@IdTour
GO

if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_layChuongTrinhTour')
	drop proc spu_tourDuLichNuocNgoai_layChuongTrinhTour
go
CREATE PROC spu_tourDuLichNuocNgoai_layChuongTrinhTour(@IdTour DATETIME)
AS
	SELECT * FROM dbo.ChuongTrinhTourNuocNgoai c WHERE c.IdTour=@IdTour ORDER BY ThuTuNgay
GO

if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_laySoChoConNhan')
	drop proc spu_tourDuLichNuocNgoai_laySoChoConNhan
go
CREATE PROC spu_tourDuLichNuocNgoai_laySoChoConNhan(@IdTour DATETIME)
AS
	SELECT SoChoConNhan
	FROM dbo.TourDuLichNuocNgoai 
	WHERE Id=@IdTour
GO

if exists (select name from sysobjects where name ='spu_tourDuLichNuocNgoai_layTheoId')
	drop proc spu_tourDuLichNuocNgoai_layTheoId
go
CREATE PROC spu_tourDuLichNuocNgoai_layTheoId(@IdTour DATETIME)
AS
	SELECT Id,MaTour,TenTour,NoiDen,NoiDi,NgayGioDi,NgayGioVe,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay,ChuyenBayDi,ChuyenBayVe,NoiTapTrung,NgayGioTapTrung,GiaTourCoBanNguoiLon,GiaTourCoBanTreEm,GiaTourCoBanTreNho,PhuThuXangDauNguoiLon,PhuThuXangDauTreEm
	,PhuThuXangDauTreNho,LePhiSanBayNguoiLon,LePhiSanBayTreEm,LePhiSanBayTreNho,NgayGioHopDoan,PhuThuVisaTaiNhapVietNam,PhuThuPhongDon,PhuThuVisaNguoiLon,PhuThuVisaTreEm,PhuThuVisaTreNho,GiaTourCoBanNguoiLon AS Gia
	FROM dbo.TourDuLichNuocNgoai 
	WHERE Id=@IdTour
GO

/* tour trong nuoc--------------------------------------- */
if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_capNhatSoLuongConNhanTheoId')
	drop proc spu_tourDuLichTrongNuoc_capNhatSoLuongConNhanTheoId
go
CREATE PROC spu_tourDuLichTrongNuoc_capNhatSoLuongConNhanTheoId(@SoChoConNhan INT,@Id DATETIME)
AS
	UPDATE dbo.TourDuLichTrongNuoc SET SoChoConNhan=@SoChoConNhan WHERE Id=@Id
GO

if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_laySoLuong')
	drop proc spu_tourDuLichTrongNuoc_laySoLuong
go
CREATE PROC spu_tourDuLichTrongNuoc_laySoLuong
(
	@NoiDi NVARCHAR(50),
	@NoiDen NVARCHAR(50),
	@NgayGioDi DATETIME,
	@MinGiaTourCoBanNguoiLonVietNam DECIMAL,
	@MaxGiaTourCoBanNguoiLonVietNam DECIMAL
	)
AS
	select COUNT(*)
	from dbo.TourDuLichTrongNuoc
	where (@NoiDi ='' OR NoiDi = @NoiDi) AND (@NoiDen ='' OR NoiDen = @NoiDen) AND (@MinGiaTourCoBanNguoiLonVietNam IS NULL OR GiaTourCoBanNguoiLonVietNam >= @MinGiaTourCoBanNguoiLonVietNam) AND (@MaxGiaTourCoBanNguoiLonVietNam IS NULL OR GiaTourCoBanNguoiLonVietNam <= @MaxGiaTourCoBanNguoiLonVietNam) AND (@NgayGioDi IS NULL OR (DAY(NgayGioDi)=DAY(@NgayGioDi) AND MONTH(NgayGioDi)=MONTH(@NgayGioDi) AND YEAR(NgayGioDi)=YEAR(@NgayGioDi))) AND TinhTrangTour=0
GO

if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_traCuu')
	drop proc spu_tourDuLichTrongNuoc_traCuu
go
CREATE PROCEDURE spu_tourDuLichTrongNuoc_traCuu
	(
	@NoiDi NVARCHAR(50),
	@NoiDen NVARCHAR(50),
	@NgayGioDi DATETIME,
	@MinGiaTourCoBanNguoiLonVietNam DECIMAL,
	@MaxGiaTourCoBanNguoiLonVietNam DECIMAL,
	@start INT,
	@end INT
	)
AS
	with Rows as
	(
		select Id,MaTour,TenTour,NoiDen,NoiDi AS NoiKhoiHanh,NgayGioDi,NgayGioVe,GiaTourCoBanNguoiLonVietNam AS Gia,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay,ROW_NUMBER() Over (order by NgayGioDi) as Row
		from dbo.TourDuLichTrongNuoc
		where (@NoiDi ='' OR NoiDi = @NoiDi) AND (@NoiDen ='' OR NoiDen = @NoiDen) AND (@MinGiaTourCoBanNguoiLonVietNam IS NULL OR GiaTourCoBanNguoiLonVietNam >= @MinGiaTourCoBanNguoiLonVietNam) AND (@MaxGiaTourCoBanNguoiLonVietNam IS NULL OR GiaTourCoBanNguoiLonVietNam <= @MaxGiaTourCoBanNguoiLonVietNam) AND (@NgayGioDi IS NULL OR (DAY(NgayGioDi)=DAY(@NgayGioDi) AND MONTH(NgayGioDi)=MONTH(@NgayGioDi) AND YEAR(NgayGioDi)=YEAR(@NgayGioDi))) AND TinhTrangTour=0
	)
	select top (@end) Id,MaTour,TenTour,NoiDen,NoiKhoiHanh,NgayGioDi,NgayGioVe,Gia,SoChoConNhan,SoNgay
	from Rows
	where Row>@start
	--SELECT Id,MaTour,TenTour,NgayGioDi,NgayGioVe,GiaTourCoBanNguoiLonVietNam AS Gia,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay
	--FROM dbo.TourDuLichTrongNuoc
	--WHERE (@NoiDi ='' OR NoiDi = @NoiDi) AND (@NoiDen ='' OR NoiDen = @NoiDen) AND (@MinGiaTourCoBanNguoiLonVietNam IS NULL OR GiaTourCoBanNguoiLonVietNam >= @MinGiaTourCoBanNguoiLonVietNam) AND (@MaxGiaTourCoBanNguoiLonVietNam IS NULL OR GiaTourCoBanNguoiLonVietNam <= @MaxGiaTourCoBanNguoiLonVietNam) AND (@NgayGioDi IS NULL OR (DAY(@NgayGioDi)=DAY(NgayGioDi) AND MONTH(@NgayGioDi)=MONTH(NgayGioDi) AND YEAR(@NgayGioDi)=YEAR(NgayGioDi))) AND TinhTrangTour=0
GO

if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_layChiTietTour')
	drop proc spu_tourDuLichTrongNuoc_layChiTietTour
go
CREATE PROCEDURE spu_tourDuLichTrongNuoc_layChiTietTour
	(
	@id DATETIME
	)
AS
	SELECT Tour.Id,MaTour,TenTour,NoiDen,NoiDi,NgayGioDi,NgayGioVe,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay,ChuyenBayDi,ChuyenBayVe,HDV.HoTen,HDV.DiaChi,HDV.DienThoai,NoiTapTrung,NgayGioTapTrung,GiaPhuThuPhongDonVietKieu,GiaPhuThuPhongDonVietNam,GiaPhuThuPhongDonNuocNgoai,GiaTourCoBanNguoiLonNuocNgoai,GiaTourCoBanNguoiLonVietKieu,GiaTourCoBanNguoiLonVietNam,GiaTourCoBanTreEmNuocNgoai,GiaTourCoBanTreEmVietKieu,GiaTourCoBanTreEmVietNam
	,GiaTourCoBanTreNhoNuocNgoai,GiaTourCoBanTreNhoVietKieu,GiaTourCoBanTreNhoVietNam
	FROM dbo.TourDuLichTrongNuoc Tour LEFT JOIN dbo.HuongDanVienDuLich HDV ON Tour.IdHuongDanVienDuLich = HDV.Id 
	WHERE Tour.Id = @Id
GO

if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_layChuongTrinhTour')
	drop proc spu_tourDuLichTrongNuoc_layChuongTrinhTour
go
CREATE PROCEDURE spu_tourDuLichTrongNuoc_layChuongTrinhTour
	(
	@id DATETIME
	)
AS
	SELECT * FROM dbo.ChuongTrinhTourTrongNuoc CT WHERE CT.IdTour=@id ORDER BY ThuTuNgay
GO

if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_laySoChoConNhan')
	drop proc spu_tourDuLichTrongNuoc_laySoChoConNhan
go
CREATE PROC spu_tourDuLichTrongNuoc_laySoChoConNhan(@IdTour DATETIME)
AS
	SELECT SoChoConNhan
	FROM dbo.TourDuLichTrongNuoc 
	WHERE Id=@IdTour
GO

if exists (select name from sysobjects where name ='spu_tourDuLichTrongNuoc_layTheoId')
	drop proc spu_tourDuLichTrongNuoc_layTheoId
go
CREATE PROC spu_tourDuLichTrongNuoc_layTheoId(@id DATETIME)
AS
	SELECT Id,MaTour,TenTour,NoiDen,NoiDi,NgayGioDi,NgayGioVe,SoChoConNhan,DATEDIFF(DAY,NgayGioDi,NgayGioVe) AS SoNgay,ChuyenBayDi,ChuyenBayVe,NoiTapTrung,NgayGioTapTrung,GiaPhuThuPhongDonVietKieu,GiaPhuThuPhongDonVietNam,GiaPhuThuPhongDonNuocNgoai,GiaTourCoBanNguoiLonNuocNgoai,GiaTourCoBanNguoiLonVietKieu,GiaTourCoBanNguoiLonVietNam,GiaTourCoBanTreEmNuocNgoai,GiaTourCoBanTreEmVietKieu,GiaTourCoBanTreEmVietNam
	,GiaTourCoBanTreNhoNuocNgoai,GiaTourCoBanTreNhoVietKieu,GiaTourCoBanTreNhoVietNam,GiaTourCoBanNguoiLonVietNam AS Gia
	FROM dbo.TourDuLichTrongNuoc 
	WHERE Id=@id
GO

/* Phieu dang ky tour nuoc ngoai */
if exists (select name from sysobjects where name ='spu_phieuDangKyTourNuocNgoai_layByMaPhieu')
	drop proc spu_phieuDangKyTourNuocNgoai_layByMaPhieu
go
CREATE PROC spu_phieuDangKyTourNuocNgoai_layByMaPhieu(@MaPhieu UNIQUEIDENTIFIER)
AS
	SELECT * FROM dbo.PhieuDangKyTourNuocNgoai WHERE MaPhieu=@MaPhieu
go

if exists (select name from sysobjects where name ='spu_chiTietPhieuDangKyTourNuocNgoai_layByIdPhieuDangKy')
	drop proc spu_chiTietPhieuDangKyTourNuocNgoai_layByIdPhieuDangKy
go
CREATE PROC spu_chiTietPhieuDangKyTourNuocNgoai_layByIdPhieuDangKy(@IdPhieuDangKy DATETIME)
AS
	SELECT * FROM dbo.ChiTietPhieuDangKyTourNuocNgoai WHERE IdPhieuDangKyTourNuocNgoai=@IdPhieuDangKy
go

if exists (select name from sysobjects where name ='spu_phieuDangKyTourNuocNgoai_them')
	drop proc spu_phieuDangKyTourNuocNgoai_them
go
CREATE PROC spu_phieuDangKyTourNuocNgoai_them(@Id DATETIME,@MaPhieu UNIQUEIDENTIFIER,@TongSoKhach INT,@SoNguoiLon INT,@SoTreEm INT,@SoTreNho INT,@TongTien DECIMAL,@IdTour DATETIME,@HoTenNguoiDangKy NVARCHAR(100),@DiaChi NVARCHAR(150),@DienThoai NCHAR(15),@Email NVARCHAR(100),@GhiChu NVARCHAR(100))
AS
	INSERT INTO dbo.PhieuDangKyTourNuocNgoai
	        ( Id ,
	          MaPhieu ,
	          TongSoKhach ,
	          SoNguoiLon ,
	          SoTreEm ,
	          SoTreNho ,
	          TongTien ,
	          IdTour ,
	          HoTenNguoiDangKy ,
	          DiaChi ,
	          DienThoai ,
	          Email ,
	          TinhTrangPhieu ,
	          GhiChu
	        )
	VALUES  ( @Id , -- Id - datetime
	          @MaPhieu , -- MaPhieu - uniqueidentifier
	          @TongSoKhach , -- TongSoKhach - int
	          @SoNguoiLon , -- SoNguoiLon - int
	          @SoTreEm , -- SoTreEm - int
	          @SoTreNho , -- SoTreNho - int
	          @TongTien , -- TongTien - decimal
	          @IdTour , -- IdTour - datetime
	          @HoTenNguoiDangKy , -- HoTenNguoiDangKy - nvarchar(100)
	          @DiaChi , -- DiaChi - nvarchar(150)
	          @DienThoai , -- DienThoai - nchar(15)
	          @Email , -- Email - nvarchar(100)
	          0 , -- TinhTrangPhieu - tinyint
	          @GhiChu  -- GhiChu - nvarchar(100)
	        )
GO

if exists (select name from sysobjects where name ='spu_chiTietPhieuDangKyTourNuocNgoai_them')
	drop proc spu_chiTietPhieuDangKyTourNuocNgoai_them
go
CREATE PROC spu_chiTietPhieuDangKyTourNuocNgoai_them(@Id DATETIME,@IdPhieuDangKy DATETIME,@HoTenKhachHang NVARCHAR(100),@NgaySinh DATETIME,@GioiTinh TINYINT,@DiaChi NVARCHAR(150),@LoaiKhach TINYINT,@DoTuoi TINYINT,@QuocTich NVARCHAR(30),@SoPassport NCHAR(30),@NgayCap DATETIME,@NgayHetHan DATETIME,@PhongDon TINYINT,@PhuThuVisaTaiNhapVN TINYINT,@Gia DECIMAL)
AS
	INSERT INTO dbo.ChiTietPhieuDangKyTourNuocNgoai
	        ( Id ,
	          IdPhieuDangKyTourNuocNgoai ,
	          HoTenKhachHang ,
	          NgaySinh ,
	          GioiTinh ,
	          DiaChi ,
	          LoaiKhach ,
	          DoTuoi ,
	          QuocTich ,
	          SoPassport ,
	          NgayCap ,
	          NgayHetHan ,
	          PhongDon ,
	          PhuThuVisaTaiNhapVN ,
	          Gia
	        )
	VALUES  ( @Id , -- Id - datetime
	          @IdPhieuDangKy , -- IdPhieuDangKyTourNuocNgoai - datetime
	          @HoTenKhachHang , -- HoTenKhachHang - nvarchar(100)
	          @NgaySinh , -- NgaySinh - datetime
	          @GioiTinh , -- GioiTinh - tinyint
	          @DiaChi , -- DiaChi - nvarchar(150)
	          @LoaiKhach , -- LoaiKhach - tinyint
	          @DoTuoi , -- DoTuoi - tinyint
	          @QuocTich , -- QuocTich - nvarchar(30)
	          @SoPassport , -- SoPassport - nchar(30)
	          @NgayCap , -- NgayCap - datetime
	          @NgayHetHan , -- NgayHetHan - datetime
	          @PhongDon , -- PhongDon - tinyint
	          @PhuThuVisaTaiNhapVN , -- PhuThuVisaTaiNhapVN - tinyint
	          @Gia  -- Gia - decimal
	        )
GO

/* Phieu dang ky tour trong nuoc */
if exists (select name from sysobjects where name ='spu_phieuDangKyTourTrongNuoc_layByMaPhieu')
	drop proc spu_phieuDangKyTourTrongNuoc_layByMaPhieu
go
CREATE PROC spu_phieuDangKyTourTrongNuoc_layByMaPhieu(@MaPhieu UNIQUEIDENTIFIER)
AS
	SELECT * FROM dbo.PhieuDangKyTourTrongNuoc WHERE MaPhieu=@MaPhieu
go

if exists (select name from sysobjects where name ='spu_chiTietPhieuDangKyTourTrongNuoc_layByIdPhieuDangKy')
	drop proc spu_chiTietPhieuDangKyTourTrongNuoc_layByIdPhieuDangKy
go
CREATE PROC spu_chiTietPhieuDangKyTourTrongNuoc_layByIdPhieuDangKy(@IdPhieuDangKy DATETIME)
AS
	SELECT * FROM dbo.ChiTietPhieuDangKyTourTrongNuoc WHERE IdPhieuDangKyTourTrongNuoc=@IdPhieuDangKy
go

if exists (select name from sysobjects where name ='spu_phieuDangKyTourTrongNuoc_them')
	drop proc spu_phieuDangKyTourTrongNuoc_them
go
CREATE PROC spu_phieuDangKyTourTrongNuoc_them(@Id DATETIME,@MaPhieu UNIQUEIDENTIFIER,@TongSoKhach INT,@SoNguoiLon INT,@SoTreEm INT,@SoTreNho INT,@TongTien DECIMAL,@IdTour DATETIME,@HoTenNguoiDangKy NVARCHAR(100),@DiaChi NVARCHAR(150),@DienThoai NCHAR(15),@Email NVARCHAR(100),@GhiChu NVARCHAR(100))
AS
	INSERT INTO dbo.PhieuDangKyTourTrongNuoc
	        ( Id ,
	          MaPhieu ,
	          TongSoKhach ,
	          SoNguoiLon ,
	          SoTreEm ,
	          SoTreNho ,
	          TongTien ,
	          IdTour ,
	          HoTenNguoiDangKy ,
	          DiaChi ,
	          DienThoai ,
	          Email ,
	          TinhTrangPhieu ,
	          GhiChu
	        )
	VALUES  ( @Id , -- Id - datetime
	          @MaPhieu , -- MaPhieu - uniqueidentifier
	          @TongSoKhach , -- TongSoKhach - int
	          @SoNguoiLon , -- SoNguoiLon - int
	          @SoTreEm , -- SoTreEm - int
	          @SoTreNho , -- SoTreNho - int
	          @TongTien , -- TongTien - decimal
	          @IdTour , -- IdTour - datetime
	          @HoTenNguoiDangKy , -- HoTenNguoiDangKy - nvarchar(100)
	          @DiaChi , -- DiaChi - nvarchar(150)
	          @DienThoai , -- DienThoai - nchar(15)
	          @Email , -- Email - nvarchar(100)
	          0 , -- TinhTrangPhieu - tinyint
	          @GhiChu  -- GhiChu - nvarchar(100)
	        )
GO

if exists (select name from sysobjects where name ='spu_chiTietPhieuDangKyTourTrongNuoc_them')
	drop proc spu_chiTietPhieuDangKyTourTrongNuoc_them
go
CREATE PROC spu_chiTietPhieuDangKyTourTrongNuoc_them(@Id DATETIME,@IdPhieuDangKy DATETIME,@HoTenKhachHang NVARCHAR(100),@NgaySinh DATETIME,@GioiTinh TINYINT,@DiaChi NVARCHAR(150),@LoaiKhach TINYINT,@DoTuoi TINYINT,@QuocTich NVARCHAR(30),@SoPassport NCHAR(30),@NgayCap DATETIME,@NgayHetHan DATETIME,@PhongDon TINYINT,@PhuThuVisaTaiNhapVN TINYINT,@Gia DECIMAL)
AS
	INSERT INTO dbo.ChiTietPhieuDangKyTourTrongNuoc
	        ( Id ,
	          IdPhieuDangKyTourTrongNuoc ,
	          HoTenKhachHang ,
	          NgaySinh ,
	          GioiTinh ,
	          DiaChi ,
	          LoaiKhach ,
	          DoTuoi ,
	          QuocTich ,
	          SoPassport ,
	          NgayCap ,
	          NgayHetHan ,
	          PhongDon ,
	          PhuThuVisaTaiNhapVN ,
	          Gia
	        )
	VALUES  ( @Id , -- Id - datetime
	          @IdPhieuDangKy , -- IdPhieuDangKyTourNuocNgoai - datetime
	          @HoTenKhachHang , -- HoTenKhachHang - nvarchar(100)
	          @NgaySinh , -- NgaySinh - datetime
	          @GioiTinh , -- GioiTinh - tinyint
	          @DiaChi , -- DiaChi - nvarchar(150)
	          @LoaiKhach , -- LoaiKhach - tinyint
	          @DoTuoi , -- DoTuoi - tinyint
	          @QuocTich , -- QuocTich - nvarchar(30)
	          @SoPassport , -- SoPassport - nchar(30)
	          @NgayCap , -- NgayCap - datetime
	          @NgayHetHan , -- NgayHetHan - datetime
	          @PhongDon , -- PhongDon - tinyint
	          @PhuThuVisaTaiNhapVN , -- PhuThuVisaTaiNhapVN - tinyint
	          @Gia  -- Gia - decimal
	        )
GO
